package com.bci.pwtz.admin.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.bci.pwtz.admin.service.AdminExcelUtilService;
import com.bci.pwtz.common.util.PwtzUtils;
import com.bci.pwtz.exception.PwtzDatabaseException;
import com.bci.pwtz.mysql.model.Company;
import com.bci.pwtz.mysql.model.UserCompanyRef;

public class AdminExcelUtilServiceImpl implements AdminExcelUtilService {

	private static final Logger _logger = Logger
			.getLogger(AdminExcelUtilServiceImpl.class);

	@Override
	public String exportCompanyStatistics(List<Company> list,String path) throws PwtzDatabaseException {
		File file = new File(path);
		if(!file.exists()){
			file.mkdirs();
		}
		
		// 返回成功失败表示:2为失败
		String returnCode = "2";
		
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("单位统计");
		if(list.size()>0){
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell(0);
			sheet.autoSizeColumn(0, true);
			cell.setCellValue("序号");
			cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("单位名称");
			cell = row.createCell(2);
			sheet.autoSizeColumn(2, true);
			cell.setCellValue("加入时间");
			cell = row.createCell(3);
			sheet.autoSizeColumn(3, true);
			cell.setCellValue("地区");
			cell = row.createCell(4);
			sheet.autoSizeColumn(4, true);
			cell.setCellValue("上级单位");
			cell = row.createCell(5);
			sheet.autoSizeColumn(5, true);
			cell.setCellValue("付费状态");
			cell = row.createCell(6);
			sheet.autoSizeColumn(6, true);
			cell.setCellValue("联系人");
			cell = row.createCell(7);
			sheet.autoSizeColumn(7, true);
			cell.setCellValue("联系人电话");
			
			for(int i=0;i<list.size();i++){
				Company company = list.get(i);
				row = sheet.createRow(i+1);
				cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(i+1);
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                cell.setCellValue(company.getCompanyName());
    			cell = row.createCell(2);
    			sheet.autoSizeColumn(2, true);
    			cell.setCellValue(PwtzUtils.date2String(company.getCreateTime()));
    			cell = row.createCell(3);
    			sheet.autoSizeColumn(3, true);
    			cell.setCellValue(company.getAddress());
    			cell = row.createCell(4);
    			sheet.autoSizeColumn(4, true);
    			cell.setCellValue(company.getParentCompanyName());
    			cell = row.createCell(5);
    			sheet.autoSizeColumn(5, true);
    			cell.setCellValue(company.getVip()==1?"付费":"试用");
    			cell = row.createCell(6);
    			sheet.autoSizeColumn(6, true);
    			cell.setCellValue(company.getContactPerson());
    			cell = row.createCell(7);
    			sheet.autoSizeColumn(7, true);
    			cell.setCellValue(company.getTel());
				
			}
		}
		
        String dir = "";
        String fileName = "";
        fileName = System.currentTimeMillis() + PwtzUtils.getRandomNumber(4)+ ".xls";
        dir = path + fileName;
		FileOutputStream os = null;
		try {
		    File newFile=new File(dir);
            if(!newFile.exists()){
                newFile.createNewFile();
            }
            os = new FileOutputStream(newFile);
			wb.write(os);
			os.close();
			returnCode = dir;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		_logger.info("returnCode =======" + returnCode);
		return returnCode;
	}

	@Override
	public String exportCompanyListStatistics(List<Company> list, String path)
			throws PwtzDatabaseException {
		// 返回成功失败表示:2为失败
		String returnCode = "2";
		
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("单位统计");
		if(list.size()>0){
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell(0);
			sheet.autoSizeColumn(0, true);
			cell.setCellValue("序号");
			cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("地区");
			cell = row.createCell(2);
			sheet.autoSizeColumn(2, true);
			cell.setCellValue("加入单位数");
			
			int count = 0;
			for(int i=0;i<list.size();i++){
				row = sheet.createRow(i+1);
				cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(i+1);
				cell = row.createCell(1);
				sheet.autoSizeColumn(1, true);
				cell.setCellValue(list.get(i).getAddress());
				cell = row.createCell(2);
				sheet.autoSizeColumn(2, true);
				cell.setCellValue(list.get(i).getNumber());
				
				count += list.get(i).getNumber();
			}
			row = sheet.createRow(list.size()+1);
			cell = row.createCell(0);
			sheet.autoSizeColumn(0, true);
			cell.setCellValue("合计");
			cell = row.createCell(2);
			sheet.autoSizeColumn(2, true);
			cell.setCellValue(count);
		}
		
        String dir = "";
        String fileName = "";
        fileName = System.currentTimeMillis() + PwtzUtils.getRandomNumber(4)+ ".xls";
        dir = path + fileName;
		FileOutputStream os = null;
		try {
		    File pathfile=new File(path);
		    if(!pathfile.exists()){
		        pathfile.mkdirs();
		    }
		    File newFile=new File(dir);
            if(!newFile.exists()){
                newFile.createNewFile();
            }
            os = new FileOutputStream(newFile);
			wb.write(os);
			os.close();
			returnCode = dir;
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		_logger.info("returnCode =======" + returnCode);
		return returnCode;
	}

    @Override
    public String exportUserstatics(List<UserCompanyRef> list, String path) throws PwtzDatabaseException
    {
     // 返回成功失败表示:2为失败
        String returnCode = "2";
        
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("用户统计");
        if(list.size()>0){
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("序号");
            cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("公司名称");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue("用户数量");
            
            int count = 0;
            for(int i=0;i<list.size();i++){
                row = sheet.createRow(i+1);
                cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(i+1);
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                cell.setCellValue(list.get(i).getCompany().getCompanyName());
                cell = row.createCell(2);
                sheet.autoSizeColumn(2, true);
                cell.setCellValue(list.get(i).getNumber());
                
                count += list.get(i).getNumber();
            }
            row = sheet.createRow(list.size()+1);
            cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("合计");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue(count);
        }
        
        String dir = "";
        String fileName = "";
        fileName = System.currentTimeMillis() + PwtzUtils.getRandomNumber(4)+ ".xls";
        File file=new File(path);
        if(!file.exists()){
            file.mkdirs();
        }
        dir = path + fileName;
        FileOutputStream os = null;
        try {
            File newFile=new File(dir);
            if(!newFile.exists()){
                newFile.createNewFile();
            }
            os = new FileOutputStream(newFile);
            wb.write(os);
            os.close();
            returnCode = dir;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        _logger.info("returnCode =======" + returnCode);
        return returnCode;
    }

    @Override
    public String exportCompanyUserList(List<UserCompanyRef> list, String path) throws PwtzDatabaseException
    {
        // 返回成功失败表示:2为失败
        String returnCode = "2";
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("公司员工统计");
        if(list.size()>0){
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("序号");
            cell = row.createCell(1);
            sheet.autoSizeColumn(1, true);
            cell.setCellValue("公司名称");
            cell = row.createCell(2);
            sheet.autoSizeColumn(2, true);
            cell.setCellValue("用户名称");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue("联系方式");
            
            int count = 0;
            for(int i=0;i<list.size();i++){
                row = sheet.createRow(i+1);
                cell = row.createCell(0);
                sheet.autoSizeColumn(0, true);
                cell.setCellValue(i+1);
                cell = row.createCell(1);
                sheet.autoSizeColumn(1, true);
                cell.setCellValue(list.get(i).getCompany().getCompanyName());
                cell = row.createCell(2);
                sheet.autoSizeColumn(2, true);
                cell.setCellValue(list.get(i).getUser().getUserName());
                cell = row.createCell(3);
                sheet.autoSizeColumn(3, true);
                cell.setCellValue(list.get(i).getUser().getMobile());
                
                count++;
            }
            row = sheet.createRow(list.size()+1);
            cell = row.createCell(0);
            sheet.autoSizeColumn(0, true);
            cell.setCellValue("合计");
            cell = row.createCell(3);
            sheet.autoSizeColumn(3, true);
            cell.setCellValue(count);
        }
        String dir = "";
        String fileName = "";
        fileName = list.get(0).getCompany().getCompanyName()+ ".xls";
//        fileName = "1111.xls";
        File file=new File(path);
        if(!file.exists()){
            file.mkdirs();
        }
        dir = path + fileName;
        FileOutputStream os = null;
        try {
            File newFile=new File(dir);
            if(!newFile.exists()){
                newFile.createNewFile();
            }
            os = new FileOutputStream(newFile);
            wb.write(os);
            os.close();
            returnCode = dir;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        _logger.info("returnCode =======" + returnCode);
        return returnCode;
    }
}